﻿using System;
using System.Linq;
using System.Data.Linq;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using ConsolidationTool.Common;
using System.Collections.Generic;
using System.Web.UI.WebControls;
using System.Collections;
using ConsolidationTool.WebParts.ControlTemplates.ConsolidationTool.WebParts;
using System.Web.UI;
using System.Web;

namespace ConsolidationTool.WebParts.Layouts.CReport
{
    [Serializable]
    public class MasterDataEntity
    {
        public MasterDataEntity(SPListItem item)
        {
            ParentCode = item["ParentCode"] + "";
            Code = item["Code"] + "";
            Title = item["Title"] + "";
            Description = item["Description"] + "";
            Category = item["Category"] + "";
        }
        public bool isRootNode { get { return ParentCode.IsNullOrEmpty(); } }
        public string ParentCode { get; set; }
        public string Code { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public string Category { get; set; }
    }

    [Serializable]
    public class LoadingData
    {
        public LoadingData(SPListItem item)
        {
            var allPro = this.GetType().GetProperties();
            allPro.ToList().ForEach(p =>
            {
                if (p.PropertyType == typeof(string))
                {
                    string tmp = BusinessService.GetFieldValue(item, p.Name);
                    p.SetValue(this, fixValue(tmp), null);
                }
            });
        }
        public string fixValue(string tmp)
        {
            return tmp;
            //decimal dec = 0.00M;
            //if (decimal.TryParse(tmp, out dec))
            //{
            //    return dec.ToString("N0");
            //}
            //return tmp;
        }
        #region all Properties

        public string Company { get; set; }
        public string Year { get; set; }
        public string Period { get; set; }
        public string Customercode { get; set; }
        public string Customername { get; set; }
        public string RGC { get; set; }
        public string Articlenumber { get; set; }
        public string Product { get; set; }
        public string Grade { get; set; }
        public string IE { get; set; }
        public string Destination { get; set; }
        public string DelivercountryGrp { get; set; }
        public string Industrialapplicat { get; set; }
        public string ANonA { get; set; }
        public string BusinessUnit { get; set; }
        public string Qty { get; set; }
        public string Salesturnover { get; set; }
        public string Freight { get; set; }
        public string Packaging { get; set; }
        public string Exportrefund { get; set; }
        public string Packing { get; set; }
        public string Rebates { get; set; }
        public string Warehouse { get; set; }
        public string Commissions { get; set; }
        public string ISOtankrental { get; set; }
        public string SBEW { get; set; }
        public string VC { get; set; }
        public string VM { get; set; }
        public string PlantNet { get; set; }
        public string Flogistics { get; set; }
        public string SellingCosts { get; set; }
        public string Development { get; set; }
        public string AdministrationCosts { get; set; }
        public string RDexclDepreciatio { get; set; }
        public string EBITDA { get; set; }
        public string PlantDepreciation { get; set; }
        public string SalesDepreciation { get; set; }
        public string AdminDepreciations { get; set; }
        public string RDDepreciations { get; set; }
        public string TotalDEP { get; set; }
        public string Operationprofit { get; set; }
        public string CommercContributio { get; set; }
        public string Commercialemargin { get; set; }

        #endregion

        public decimal SBEWCost { get { return BusinessService.decimalParse(Freight) + BusinessService.decimalParse(Packaging) + BusinessService.decimalParse(Exportrefund) + BusinessService.decimalParse(Packing) + BusinessService.decimalParse(Rebates) + BusinessService.decimalParse(Warehouse) + BusinessService.decimalParse(Commissions) + BusinessService.decimalParse(ISOtankrental); } }
        public decimal VMin { get; set; }
    }
    [Serializable]
    public class TrackItem
    {
        public TrackItem() { }
        public TrackItem(List<string> dates)
        {
            values = new Dictionary<string, decimal>();
            dates.ForEach(p =>
            {
                values.Add(p, 0);
            });
        }

        public string rgc { get; set; }
        public string cusomercode { get; set; }
        public string unit { get; set; }
        public string column { get; set; }
        public Dictionary<string, decimal> values { get; set; }
        private List<string> _datasource;
        public List<string> datasource
        {
            get
            {
                if (_datasource == null)
                {
                    string fix = this.unit == "%" ? "3" : "0";
                    fix = this.column == "Qty" ? "2" : fix;
                    _datasource = new List<string> { };
                    values.Values.ToList().ForEach(c => _datasource.Add(c.ToString("N" + fix)));
                    _datasource[_datasource.Count - 2] = ""; //set the field to empty
                }
                return _datasource;
            }
        }

        public string field { get; set; }
    }
    [Serializable]
    public class TrackRow
    {
        public TrackRow(string lastFieldName)
        {
            this.LastFieldName = lastFieldName;
            Fields = new List<TrackItem>();
        }
        /// <summary>
        /// 所有列表项的第一个条件
        /// </summary>
        public string FirstSeed { get; set; }
        /// <summary>
        /// 所有列表项的第二个条件
        /// </summary>
        public string SecondSeed { get; set; }
        public string LastFieldName { get; set; }

        //all sort seed
        public decimal MRTurnover { get { return Fields.Find(c => c.field == "Turnover").values[LastFieldName]; } }
        public decimal MRVolume { get { return Fields.Find(c => c.field == "Qty").values[LastFieldName]; } }
        public decimal MRPrice { get { return Fields.Find(c => c.field == "SBEWPrice").values[LastFieldName]; } }
        public decimal MRSBEWCost { get { return Fields.Find(c => c.field == "SBEWCost").values[LastFieldName]; } }



        public decimal MRVM { get { return Fields.Find(c => c.field == "VM").values[LastFieldName]; } }
        /// <summary>
        /// in detail last turnover is unit price
        /// </summary>
        public decimal LastMTurnover { get { return Fields.Find(c => c.field == "Turnover").values.Values.ToArray()[11]; } }
        public decimal LastMVolume { get { return Fields.Find(c => c.field == "Qty").values.Values.ToArray()[11]; } }
        public decimal LastMVM { get { return Fields.Find(c => c.field == "VM").values.Values.ToArray()[11]; } }
        public decimal LastMSBEWCost { get { return Fields.Find(c => c.field == "SBEWCost").values.Values.ToArray()[11]; } }

        public List<TrackItem> Fields { get; set; }

    }
    [Serializable]
    public class RGCTracking
    {
        /// <summary>
        /// 所有列表项的第一个条件
        /// </summary>
        public string FirstSeed { get; set; }
        /// <summary>
        /// 所有列表项的第二个条件
        /// </summary>
        public string SecondSeed { get; set; }
        public string Date { get; set; }
        public decimal Qty { get; set; }
        public decimal Turnover { get; set; }
        public decimal SBEWCost { get; set; }
        public decimal SBEWPrice { get; set; }
        public decimal VC { get; set; }
        public decimal VM { get; set; }
        public decimal VMIN { get; set; }


        public decimal Freight { get; set; }
        public decimal Packaging { get; set; }
        public decimal Exportrefund { get; set; }
        public decimal Packing { get; set; }
        public decimal Rebates { get; set; }
        public decimal Warehouse { get; set; }
        public decimal Commissions { get; set; }
        public decimal ISOtankrental { get; set; }
    }

    public partial class VMDetailTracking : FSReportBase
    {
        #region Override
        public override ControlTemplates.CommonEntitySelector TopSelector
        {
            get
            {
                return commonEntitySelector as ControlTemplates.CommonEntitySelector;
            }
        }

        public override void BeforLoad()
        {

            base.BeforLoad();
            GVTrackingReport.ItemDataBound += new System.Web.UI.WebControls.RepeaterItemEventHandler(GVTrackingReport_ItemDataBound);
            drFilter.SelectedIndexChanged += new EventHandler(drFilter_SelectedIndexChanged);
            rbOrder.SelectedIndexChanged += new EventHandler(rbOrder_SelectedIndexChanged);
            drRGC.SelectedIndexChanged += new EventHandler(drRGC_SelectedIndexChanged);
            lbFilterCustomer.Click += new EventHandler(drRGC_SelectedIndexChanged);
            drAC.SelectedIndexChanged += new EventHandler(drAC_SelectedIndexChanged);
            btChoose.Click += new EventHandler(drRGC_SelectedIndexChanged);
            //    (ExportExcel1 as PublicExportExcel).ExportButtonClicked += new PublicExportExcel.ExportButtonClickHandler(VMDetailTracking_ExportButtonClicked);

            rgcArae.Visible = FilterByRGC || FilterByAC || FilterByRGCAndAC || FilterByRGCAndCustomer;
            acArea.Visible = FilterByRGCAndAC;
            btChoose.Visible = isOpen;

            customerArea.Visible = FilterByCustomer || FilterByRGCAndCustomer;
            InitFilter();
        }

        public override string CacheType
        {
            get
            {
                return "RGC,SubRegion";
            }
        }

        #endregion

        #region Event

        void GVTrackingReport_ItemDataBound(object sender, System.Web.UI.WebControls.RepeaterItemEventArgs e)
        {
            var rep = e.Item.FindControl("GVTrackingSubrow") as Repeater;
            if (rep == null) return;
            rep.ItemDataBound += new RepeaterItemEventHandler(rep_ItemDataBound);
            rep.DataSource = e.Item.DataItem.TryGetValue("Fields");
            rep.DataBind();
        }

        void rep_ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            var rep = e.Item.FindControl("GVTrackingRow") as Repeater;
            if (rep == null || e.Item.DataItem == null) return;
            rep.DataSource = e.Item.DataItem.TryGetValue("datasource");
            rep.DataBind();
        }

        void rbOrder_SelectedIndexChanged(object sender, EventArgs e)
        {
            GenerateReport_Click(sender, e);
        }
        void drRGC_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (sender is DropDownList)
                GenerateReport_Click("Refresh" + (sender as DropDownList).ToolTip, e);
            else
                GenerateReport_Click("Refresh" + (sender as LinkButton).ToolTip, e);
        }

        void drFilter_SelectedIndexChanged(object sender, EventArgs e)
        {
            GenerateReport_Click(sender, e);
        }
        void drAC_SelectedIndexChanged(object sender, EventArgs e)
        {
            GenerateReport_Click("RefreshAC", e);
        }

        #endregion

        #region Property
        public string TreeViewFilterString { get; set; }
        public bool Detail { get { return Request["detail"] == "true"; } }
        public bool SBEWCostTracking { get { return Request["type"] == "SBEW"; } }

        public string LastFieldName { get { return Detail ? "12M Average" : "12M Rolling"; } }
        public bool FilterByAll { get { return Request["sortfor"].IsNullOrEmpty(); } }
        public bool FilterByRGC { get { return Request["sortfor"] == "RGC"; } }
        public bool FilterByCustomer { get { return Request["sortfor"] == "Customer"; } }
        public bool FilterByAC { get { return Request["sortfor"] == "AC"; } }
        public bool FilterByRGCAndAC { get { return Request["sortfor"] == "RGC_AC"; } }
        public bool FilterByRGCAndCustomer { get { return Request["sortfor"] == "RGC_Customer"; } }

        //new
        public bool FilterByBU_RGC { get { return Request["ctrl"] == "BU_RGC"; } }
        //public bool FilterByBUGRC_Customer { get { return Request["sortfor"] == "BU_RGC_Customer"; } }

        public bool FilterByRegion_RGC { get { return Request["ctrl"] == "Region_RGC"; } }
        //public bool FilterByRegionGRC_Customer { get { return Request["sortfor"] == "Region_RGC_Customer"; } }

        public bool FilterByKeyAccount_RGC { get { return HttpUtility.UrlDecode(Request["ctrl"]) == "Key Account_RGC"; } }
        public bool isOpen { get { return Request["control"] == "true"; } }


        public List<string> _selectedCustomers;
        public List<string> SelectedCustomers
        {
            get
            {
                if (_selectedCustomers == null)
                {
                    _selectedCustomers = new List<string>();
                    for (int i = 0; i < cblCustomer.Items.Count; i++)
                    {
                        var item = cblCustomer.Items[i];
                        if (item.Selected)
                            _selectedCustomers.Add(item.Value);
                    }
                }
                return _selectedCustomers;
            }
        }

        #endregion

        #region Loader

        private List<RGCTracking> LoadDataByAll(List<LoadingData> allRow, string tmpName, string cusName)
        {
            var groupData = new List<RGCTracking>();
            if (SBEWCostTracking)
            {
                groupData = allRow.GroupBy(p => new { p.Year, p.Period, p.RGC }).Select(x => new RGCTracking()
                {
                    FirstSeed = x.Key.RGC,
                    SecondSeed = cusName,
                    Date = CreateDate(x.Key.Year, x.Key.Period),
                    Qty = x.Sum(c => BusinessService.decimalParse(c.Qty)),
                    SBEWCost = x.Sum(c => c.SBEWCost),
                    Freight = x.Sum(c => BusinessService.decimalParse(c.Freight)),
                    Packaging = x.Sum(c => BusinessService.decimalParse(c.Packaging)),
                    Exportrefund = x.Sum(c => BusinessService.decimalParse(c.Exportrefund)),
                    Packing = x.Sum(c => BusinessService.decimalParse(c.Packing)),
                    Rebates = x.Sum(c => BusinessService.decimalParse(c.Rebates)),
                    Warehouse = x.Sum(c => BusinessService.decimalParse(c.Warehouse)),
                    Commissions = x.Sum(c => BusinessService.decimalParse(c.Commissions)),
                    ISOtankrental = x.Sum(c => BusinessService.decimalParse(c.ISOtankrental))
                }).ToList();

                //get all rgc group
                var AllRGC = allRow.GroupBy(p => new { p.Year, p.Period }).Select(x => new RGCTracking()
                {
                    FirstSeed = tmpName,
                    SecondSeed = cusName,
                    Date = CreateDate(x.Key.Year, x.Key.Period),
                    Qty = x.Sum(c => BusinessService.decimalParse(c.Qty)),
                    SBEWCost = x.Sum(c => c.SBEWCost),
                    Freight = x.Sum(c => BusinessService.decimalParse(c.Freight)),
                    Packaging = x.Sum(c => BusinessService.decimalParse(c.Packaging)),
                    Exportrefund = x.Sum(c => BusinessService.decimalParse(c.Exportrefund)),
                    Packing = x.Sum(c => BusinessService.decimalParse(c.Packing)),
                    Rebates = x.Sum(c => BusinessService.decimalParse(c.Rebates)),
                    Warehouse = x.Sum(c => BusinessService.decimalParse(c.Warehouse)),
                    Commissions = x.Sum(c => BusinessService.decimalParse(c.Commissions)),
                    ISOtankrental = x.Sum(c => BusinessService.decimalParse(c.ISOtankrental))
                });
                groupData.InsertRange(0, AllRGC);
            }
            else
            {
                groupData = allRow.GroupBy(p => new { p.Year, p.Period, p.RGC }).Select(x => new RGCTracking()
                {
                    FirstSeed = x.Key.RGC,
                    SecondSeed = cusName,
                    Date = CreateDate(x.Key.Year, x.Key.Period),
                    Qty = x.Sum(c => BusinessService.decimalParse(c.Qty)),
                    Turnover = x.Sum(c => BusinessService.decimalParse(c.Salesturnover)),
                    SBEWCost = x.Sum(c => c.SBEWCost),
                    SBEWPrice = x.Sum(c => BusinessService.decimalParse(c.SBEW)),
                    VC = x.Sum(c => BusinessService.decimalParse(c.VC)),
                    VM = x.Sum(c => BusinessService.decimalParse(c.VM)),
                    VMIN = x.Sum(c => c.VMin)
                }).ToList();

                //get all rgc group
                var AllRGC = allRow.GroupBy(p => new { p.Year, p.Period }).Select(x => new RGCTracking()
                {
                    FirstSeed = tmpName,
                    SecondSeed = cusName,
                    Date = CreateDate(x.Key.Year, x.Key.Period),
                    Qty = x.Sum(c => BusinessService.decimalParse(c.Qty)),
                    Turnover = x.Sum(c => BusinessService.decimalParse(c.Salesturnover)),
                    SBEWCost = x.Sum(c => c.SBEWCost),
                    SBEWPrice = x.Sum(c => BusinessService.decimalParse(c.SBEW)),
                    VC = x.Sum(c => BusinessService.decimalParse(c.VC)),
                    VM = x.Sum(c => BusinessService.decimalParse(c.VM)),
                    VMIN = x.Sum(c => c.VMin)
                });
                groupData.InsertRange(0, AllRGC);
            }
            return groupData;
        }
        /// <summary>
        /// 指定rgc下分客户汇总
        /// </summary>
        /// <param name="allRow"></param>
        /// <returns></returns>
        private List<RGCTracking> LoadDataByRGC(List<LoadingData> allRow, string selectedRgc)
        {
            var groupData = new List<RGCTracking>();
            if (SBEWCostTracking)
            {
                groupData = allRow.GroupBy(p => new { p.Year, p.Period, p.Customername, p.Customercode }).Select(x => new RGCTracking()
                {
                    FirstSeed = x.Key.Customername,
                    SecondSeed = x.Key.Customercode,
                    Date = CreateDate(x.Key.Year, x.Key.Period),
                    Qty = x.Sum(c => BusinessService.decimalParse(c.Qty)),
                    SBEWCost = x.Sum(c => c.SBEWCost),
                    Freight = x.Sum(c => BusinessService.decimalParse(c.Freight)),
                    Packaging = x.Sum(c => BusinessService.decimalParse(c.Packaging)),
                    Exportrefund = x.Sum(c => BusinessService.decimalParse(c.Exportrefund)),
                    Packing = x.Sum(c => BusinessService.decimalParse(c.Packing)),
                    Rebates = x.Sum(c => BusinessService.decimalParse(c.Rebates)),
                    Warehouse = x.Sum(c => BusinessService.decimalParse(c.Warehouse)),
                    Commissions = x.Sum(c => BusinessService.decimalParse(c.Commissions)),
                    ISOtankrental = x.Sum(c => BusinessService.decimalParse(c.ISOtankrental))
                }).ToList();

                //get all rgc group
                var AllRGC = allRow.GroupBy(p => new { p.Year, p.Period }).Select(x => new RGCTracking()
                {
                    FirstSeed = selectedRgc,
                    SecondSeed = "All AC",
                    Date = CreateDate(x.Key.Year, x.Key.Period),
                    Qty = x.Sum(c => BusinessService.decimalParse(c.Qty)),
                    SBEWCost = x.Sum(c => c.SBEWCost),
                    Freight = x.Sum(c => BusinessService.decimalParse(c.Freight)),
                    Packaging = x.Sum(c => BusinessService.decimalParse(c.Packaging)),
                    Exportrefund = x.Sum(c => BusinessService.decimalParse(c.Exportrefund)),
                    Packing = x.Sum(c => BusinessService.decimalParse(c.Packing)),
                    Rebates = x.Sum(c => BusinessService.decimalParse(c.Rebates)),
                    Warehouse = x.Sum(c => BusinessService.decimalParse(c.Warehouse)),
                    Commissions = x.Sum(c => BusinessService.decimalParse(c.Commissions)),
                    ISOtankrental = x.Sum(c => BusinessService.decimalParse(c.ISOtankrental))
                });
                groupData.InsertRange(0, AllRGC);

            }
            else
            {
                groupData = allRow.GroupBy(p => new { p.Year, p.Period, p.Customername, p.Customercode }).Select(x => new RGCTracking()
                {
                    FirstSeed = x.Key.Customername,
                    SecondSeed = x.Key.Customercode,
                    Date = CreateDate(x.Key.Year, x.Key.Period),
                    Qty = x.Sum(c => BusinessService.decimalParse(c.Qty)),
                    Turnover = x.Sum(c => BusinessService.decimalParse(c.Salesturnover)),
                    SBEWCost = x.Sum(c => c.SBEWCost),
                    SBEWPrice = x.Sum(c => BusinessService.decimalParse(c.SBEW)),
                    VC = x.Sum(c => BusinessService.decimalParse(c.VC)),
                    VM = x.Sum(c => BusinessService.decimalParse(c.VM)),
                    VMIN = x.Sum(c => c.VMin)
                }).ToList();

                //get all rgc group
                var AllRGC = allRow.GroupBy(p => new { p.Year, p.Period }).Select(x => new RGCTracking()
                {
                    FirstSeed = selectedRgc,
                    SecondSeed = "All Customer",
                    Date = CreateDate(x.Key.Year, x.Key.Period),
                    Qty = x.Sum(c => BusinessService.decimalParse(c.Qty)),
                    Turnover = x.Sum(c => BusinessService.decimalParse(c.Salesturnover)),
                    SBEWCost = x.Sum(c => c.SBEWCost),
                    SBEWPrice = x.Sum(c => BusinessService.decimalParse(c.SBEW)),
                    VC = x.Sum(c => BusinessService.decimalParse(c.VC)),
                    VM = x.Sum(c => BusinessService.decimalParse(c.VM)),
                    VMIN = x.Sum(c => c.VMin)
                });
                groupData.InsertRange(0, AllRGC);
            }
            return groupData;
        }
        /// <summary>
        /// 指定rgc下分AC汇总
        /// </summary>
        /// <param name="allRow"></param>
        /// <returns></returns>
        private List<RGCTracking> LoadDataByAC(List<LoadingData> allRow, string selectedRgc)
        {
            var groupData = allRow.GroupBy(p => new { p.Year, p.Period, p.Articlenumber, p.Product }).Select(x => new RGCTracking()
                {
                    FirstSeed = x.Key.Articlenumber,
                    SecondSeed = x.Key.Product,
                    Date = CreateDate(x.Key.Year, x.Key.Period),
                    Qty = x.Sum(c => BusinessService.decimalParse(c.Qty)),
                    Turnover = x.Sum(c => BusinessService.decimalParse(c.Salesturnover)),
                    SBEWCost = x.Sum(c => c.SBEWCost),
                    SBEWPrice = x.Sum(c => BusinessService.decimalParse(c.SBEW)),
                    VC = x.Sum(c => BusinessService.decimalParse(c.VC)),
                    VM = x.Sum(c => BusinessService.decimalParse(c.VM)),
                    VMIN = x.Sum(c => c.VMin)
                }).ToList();

            //get all rgc group
            var AllRGC = allRow.GroupBy(p => new { p.Year, p.Period }).Select(x => new RGCTracking()
            {
                FirstSeed = selectedRgc,
                SecondSeed = "All AC",
                Date = CreateDate(x.Key.Year, x.Key.Period),
                Qty = x.Sum(c => BusinessService.decimalParse(c.Qty)),
                Turnover = x.Sum(c => BusinessService.decimalParse(c.Salesturnover)),
                SBEWCost = x.Sum(c => c.SBEWCost),
                SBEWPrice = x.Sum(c => BusinessService.decimalParse(c.SBEW)),
                VC = x.Sum(c => BusinessService.decimalParse(c.VC)),
                VM = x.Sum(c => BusinessService.decimalParse(c.VM)),
                VMIN = x.Sum(c => c.VMin)
            });
            groupData.InsertRange(0, AllRGC);

            return groupData;
        }
        #endregion

        #region InitSortAdditions

        private void InitFilter()
        {
            if (IsPostBack) return;

            if (FilterByAll || FilterByCustomer)
            {
                drFilter.Items.Add(new ListItem("RGC code", "FirstSeed") { Selected = true });
            }
            else if (FilterByRGC || FilterByRGCAndAC)
            {
                drFilter.Items.Add(new ListItem("CustomerName", "FirstSeed") { Selected = true });
                drFilter.Items.Add(new ListItem("CustomerCode", "SecondSeed"));
            }
            else if (FilterByAC || FilterByRGCAndCustomer)
            {
                drFilter.Items.Add(new ListItem("Article number", "FirstSeed") { Selected = true });
                drFilter.Items.Add(new ListItem("Product", "SecondSeed"));
            }

            if (SBEWCostTracking)
            {
                if (Detail)
                {
                    drFilter.Items.Add(new ListItem("12M average Volume", "MRVolume"));
                    drFilter.Items.Add(new ListItem("12M average unit SBEW Cost", "MRSBEWCost"));
                    drFilter.Items.Add(new ListItem("Last M Volume", "LastMVolume"));
                    drFilter.Items.Add(new ListItem("Last M unit SBEW Cost", "LastMSBEWCost"));
                }
                else
                {
                    drFilter.Items.Add(new ListItem("12MR Volume", "MRVolume"));
                    drFilter.Items.Add(new ListItem("12MR SBEW Cost", "MRSBEWCost"));
                    drFilter.Items.Add(new ListItem("Last M Volume", "LastMVolume"));
                    drFilter.Items.Add(new ListItem("Last M SBEW Cost", "LastMSBEWCost"));
                }
            }
            else
            {
                if (Detail)
                {
                    drFilter.Items.Add(new ListItem("12M average Volume", "MRVolume"));
                    drFilter.Items.Add(new ListItem("12M average Unit price", "MRPrice"));
                    drFilter.Items.Add(new ListItem("12M average unit VM", "MRVM"));
                    drFilter.Items.Add(new ListItem("Last M Volume", "LastMVolume"));
                    drFilter.Items.Add(new ListItem("Last M Unit price", "LastMTurnover"));
                    drFilter.Items.Add(new ListItem("Last M unit VM", "LastMVM"));
                }
                else
                {
                    drFilter.Items.Add(new ListItem("12MR Volume", "MRVolume"));
                    drFilter.Items.Add(new ListItem("12MR Turnover", "MRTurnover"));
                    drFilter.Items.Add(new ListItem("12MR VM", "MRVM"));
                    drFilter.Items.Add(new ListItem("Last M Volume", "LastMVolume"));
                    drFilter.Items.Add(new ListItem("Last M Turnover", "LastMTurnover"));
                    drFilter.Items.Add(new ListItem("Last M VM", "LastMVM"));
                }
            }
        }

        private void InitCategoryFilter(List<LoadingData> Ld, object sender)
        {
            string token = sender + "";

            if (token == "RefreshAC" || token == "RefreshCustomer") return;

            if (FilterByRGCAndAC && token == "RefreshRGC")
            {
                RefreshAC(Ld); return;
            }
            if (FilterByRGCAndCustomer && token == "RefreshRGC") //如果在指定RGC指定客户下，按AC明细汇总情况下刷新了rgc
            {
                RefreshCustomer(Ld); return;
            }

            if (token == "RefreshRGC") return;

            if (FilterByRGC || FilterByAC || FilterByRGCAndAC || FilterByRGCAndCustomer)
            {
                //    Ld.Select(p => p.RGC).Distinct().OrderBy(p => p).ToList();   //Change to just get available rgc through rgc list if the user have promission to access item
                List<string> allRGC = GetAvailableRGC();
                allRGC.ForEach(p => drRGC.Items.Add(new ListItem(p, p)));
                drRGC.SelectedIndex = 0;
            }
            if (FilterByRGCAndAC)
            {
                RefreshAC(Ld);
            }
            if (FilterByCustomer || FilterByRGCAndCustomer)
            {
                RefreshCustomer(Ld);
            }
        }

        private List<string> GetAvailableRGC()
        {
            var list = SPContext.Current.Web.Lists.TryGetList("RGC");
            if (list == null)
                throw new Exception("No RGC List in the site .");
            var tmp = new List<string>();
            var dt = list.Items.GetDataTable();
            int itemCount = dt.Rows.Count;
            for (int i = 0; i < itemCount; i++)
            {
                tmp.Add(dt.Rows[i]["RGC"] + "-" + dt.Rows[i]["Description"] + "");
            }
            return tmp;
        }

        private void RefreshCustomer(List<LoadingData> Ld)
        {
            cblCustomer.Items.Clear();
            if (FilterByRGCAndCustomer) //如果是通过RGC和Customer来筛选，在加载customer的时候就要先通过选择的rgc来过滤
            {
                var allCustomerName = Ld.Where(p => p.RGC == drRGC.SelectedValue)
                    .Select(p => new { Text = p.Customername + "&" + p.Customercode, Value = p.Customername })
                    .Distinct().OrderBy(p => p.Text).ToList();
                allCustomerName.ForEach(p => cblCustomer.Items.Add(new ListItem(p.Text, p.Value)));
            }
            else
            {
                var allCustomerName = Ld.Select(p => new { Text = p.Customername + "&" + p.Customercode, Value = p.Customername }).Distinct().OrderBy(p => p.Text).ToList();
                allCustomerName.ForEach(p => cblCustomer.Items.Add(new ListItem(p.Text, p.Value)));
            }
            cblCustomer.SelectedIndex = 0;


            /*     drCustomer.Items.Clear();
                 if (FilterByRGCAndCustomer) //如果是通过RGC和Customer来筛选，在加载customer的时候就要先通过选择的rgc来过滤
                 {
                     var allCustomerName = Ld.Where(p => p.RGC == drRGC.SelectedValue)
                         .Select(p => new { Text = p.Customername + "&" + p.Customercode, Value = p.Customername })
                         .Distinct().OrderBy(p => p.Text).ToList();
                     allCustomerName.ForEach(p => drCustomer.Items.Add(new ListItem(p.Text, p.Value)));
                 }
                 else
                 {
                     var allCustomerName = Ld.Select(p => new { Text = p.Customername + "&" + p.Customercode, Value = p.Customername }).Distinct().OrderBy(p => p.Text).ToList();
                     allCustomerName.ForEach(p => drCustomer.Items.Add(new ListItem(p.Text, p.Value)));
                 }
                 drCustomer.SelectedIndex = 0;*/
        }

        private void RefreshAC(List<LoadingData> Ld)
        {
            drAC.Items.Clear();
            var allAC = Ld.Where(p => p.RGC == drRGC.SelectedValue)
                .Select(p => new { Text = p.Articlenumber + "&" + p.Product, Value = p.Articlenumber })
                .Distinct().OrderBy(p => p.Text).ToList();
            allAC.ForEach(p => drAC.Items.Add(new ListItem(p.Text, p.Value)));
            drAC.SelectedIndex = 0;
        }


        #endregion

        private void InitFilterView()
        {
            lbInfo.Text = string.Empty;
            string filterStr = string.Empty;
            if (!drRGC.SelectedValue.IsNullOrEmpty())
                filterStr += ",<b>RGC:</b>" + drRGC.SelectedValue;

            if (SelectedCustomers.Count > 0)
            {
                filterStr += ",<b>Customer:</b>";
                SelectedCustomers.ForEach(p => { filterStr += p + ","; });
            }
            if (!drAC.SelectedValue.IsNullOrEmpty())
                filterStr += ",<b>AC:</b>" + drAC.SelectedItem.Text;

            lrFilterDescription.Text = string.Format("<b>Sort:</b>{0},<b>Order:</b>{1}  {2} {3}", drFilter.SelectedItem.Text, rbOrder.SelectedValue, filterStr, TreeViewFilterString);

        }

        List<LoadingData> allRow;

        public override void GenerateReport_Click(object sender, EventArgs e)
        {
            if (sender is Button || (sender + "").StartsWith("Refresh")) ViewState["_ART_VMTrackingDataSource"] = null;
            var VMTrackDatasource = ViewState["_ART_VMTrackingDataSource"] == null ? new List<TrackRow>() : ViewState["_ART_VMTrackingDataSource"] as List<TrackRow>;
            var Header = BusinessService.GetReportTitle(CEntity, 12);
            Header.Add("");
            Header.Add(LastFieldName);


            if (VMTrackDatasource.Count == 0)
            {
                allRow = ViewState["_ART_VMTrackingLoadingData"] == null ? new List<LoadingData>() : ViewState["_ART_VMTrackingLoadingData"] as List<LoadingData>;
                if (allRow.Count == 0)//set the loading data to viewstate. up the speed
                {
                    allRow = GetLast12MonthFieldValue(CEntity.Entity, CEntity.Year, CEntity.Month, "Actual_LoadingFile", false);

                    // ViewState["_ART_VMTrackingLoadingData"] = allRow;
                }

                allRow = FilterDataSource(allRow);

                if (allRow.Count == 0)
                {
                    lbInfo.Text = "there is no data from your selected conditions!";
                    return;
                }
                InitCategoryFilter(allRow, sender);

                var groupData = new List<RGCTracking>();
                if (FilterByAll)
                    groupData = LoadDataByAll(allRow, "All", "All Customer");
                if (FilterByRGC)
                {
                    var selectedRgc = drRGC.SelectedValue;
                    groupData = LoadDataByRGC(allRow.Where(p => p.RGC == selectedRgc).ToList(), selectedRgc);
                }

                if (FilterByRGCAndAC || (FilterByRGCAndAC && (sender + "").Equals("RefreshRGC")) || (FilterByRGCAndAC && (sender + "").StartsWith("RefreshAC")))
                {
                    var selectedRGC = drRGC.SelectedValue;
                    var selectedAC = drAC.SelectedValue;
                    groupData = LoadDataByRGC(allRow.Where(p => p.RGC == selectedRGC && p.Articlenumber == selectedAC).ToList(), selectedAC);
                }
                else if (FilterByRGCAndCustomer || (FilterByRGCAndCustomer && (sender + "").StartsWith("RefreshRGC")) || (FilterByRGCAndCustomer && (sender + "").StartsWith("RefreshCustomer")))
                {
                    var selectedRGC = drRGC.SelectedValue;
                    //  var selectedCustomer = drCustomer.SelectedValue;
                    groupData = LoadDataByAC(allRow.Where(p => p.RGC == selectedRGC && SelectedCustomers.Contains(p.Customername)).ToList(), selectedRGC);
                }

                if (FilterByCustomer)
                {
                    allRow = allRow.Where(p => SelectedCustomers.Contains(p.Customername)).ToList();
                    groupData = LoadDataByAll(allRow, SelectedCustomers.FirstOrDefault(), "All Customer");
                }
                if (FilterByAC)
                {
                    var selectedRGC = drRGC.SelectedValue;
                    groupData = LoadDataByAC(allRow.Where(p => p.RGC == selectedRGC).ToList(), selectedRGC);
                }

                groupData.ToList().ForEach(p =>
                {
                    bool newAdd;
                    var titem = CreateTrackRow(Header, VMTrackDatasource, p, out newAdd);
                    if (newAdd)
                        VMTrackDatasource.Add(titem);
                });
                if (Detail)
                    ProcessDetailData(VMTrackDatasource);

                ViewState["_ART_VMTrackingDataSource"] = VMTrackDatasource;
            }

            if (VMTrackDatasource.Count > 0)
            {
                var allRGCTrackItem = VMTrackDatasource[0];

                //process sorting 
                VMTrackDatasource = VMTrackDatasource.Skip(1).OrderBy(p => p.TryGetValue(drFilter.SelectedValue)).ToList();
                if (rbOrder.SelectedItem.Text == "Descending")
                    VMTrackDatasource = VMTrackDatasource.OrderByDescending(p => p.TryGetValue(drFilter.SelectedValue)).ToList();

                VMTrackDatasource.Insert(0, allRGCTrackItem);

                InitFilterView();
            }
            else
            {
                lbInfo.Text = "No data in RGC:" + drRGC.SelectedValue;
            }

            GVTrackingTitle.DataSource = Header;
            GVTrackingTitle.DataBind();


            GVTrackingReport.DataSource = VMTrackDatasource;
            GVTrackingReport.DataBind();
        }

        private List<LoadingData> FilterDataSource(List<LoadingData> allRow)
        {
            var selectedValues = (VMTreeControl1 as VMTreeControl).SelectedValues;
            if (selectedValues.Count == 0) return allRow;
            //btChoose.Visible = true;

            var FirstCheckedValues = (VMTreeControl1 as VMTreeControl).FirstCheckedValues;
            if (FirstCheckedValues.Count > 0)
            {
                TreeViewFilterString = ",<b>"+Request["ctrl"]+"</b>:";
                FirstCheckedValues.ForEach(p => TreeViewFilterString += (p + ","));
            }
            if (FilterByBU_RGC)
            {
                return allRow.Where(p => selectedValues.Contains(p.BusinessUnit)).ToList();
            }
            else if (FilterByKeyAccount_RGC)
            {
                return allRow.Where(p => selectedValues.Contains(p.Customercode)).ToList();
            }
            else if (FilterByRegion_RGC)
            {
                return allRow.Where(p => selectedValues.Contains(p.Destination)).ToList();
                //return allRow.Where(p =>
                //{
                //    var a = SubRegion.FirstOrDefault(c => c.Value.IndexOf(p.Company) != -1);
                //    return selectedValues.Contains(a.Key);
                //}).ToList();
            }



            return allRow;
        }

        /// <summary>
        /// 处理detail页面数据，在数据加载完最后一步处理除以qty的操作
        /// </summary>
        private void ProcessDetailData(List<TrackRow> VMTrackDatasource)
        {
            VMTrackDatasource.ForEach(p =>
            {
                p.Fields.ForEach(c =>
                {
                    if (c.field == "Qty")
                        c.values[LastFieldName] = c.values[LastFieldName] / 12;
                    else
                    {
                        var Qty = p.Fields.Find(d => d.field == "Qty");

                        c.values.ToList().ForEach(a =>
                        {
                            if (!a.Key.IsNullOrEmpty() && a.Key != LastFieldName)
                                c.values[a.Key] = Utilities.Division(c.values[a.Key], Qty.values[a.Key]);
                        });

                        //    c.values[LastFieldName] = c.values.Sum(d => d.Value) - c.values[LastFieldName];
                        c.values[LastFieldName] = Utilities.Division(c.values[LastFieldName], Qty.values.Values.Take(12).Sum());
                    }
                });

                FixVMinFieldValue(p);
            });
        }
        /// <summary>
        /// 通过行转列的方式，创建一项数据，将其中的参数通过日期列出来。
        /// </summary> 
        private TrackRow CreateTrackRow(List<string> Header, List<TrackRow> trackData, RGCTracking p, out bool newAdd)
        {
            TrackRow row = trackData.FirstOrDefault(c => c.FirstSeed == p.FirstSeed);
            newAdd = false;
            if (row == null)
            {
                newAdd = true;
                row = new TrackRow(LastFieldName)
                {
                    Fields = GetDefaultFields(Header)
                };
            }

            row.Fields.ForEach(field =>
            {
                // field.rgc = field.column == "Qty" ? p.FirstSeed : "";
                // field.cusomercode = field.column == "Qty" ? p.SecondSeed : "";
                field.rgc = p.FirstSeed;
                field.cusomercode = p.SecondSeed;

                field.values[p.Date] = BusinessService.decimalParse(p.TryGetValue(field.field) + "");

                //set last value
                field.values[LastFieldName] = field.values.Sum(d => d.Value) - field.values[LastFieldName];
            });

            //process sort seed
            row.FirstSeed = p.FirstSeed;
            row.SecondSeed = p.SecondSeed;

            FixVMinFieldValue(row);
            return row;
        }
        /// <summary>
        /// 处理VM in的数据，在每个参数的最后一个字段处理完后来处理VM in的数据
        /// </summary> 
        private void FixVMinFieldValue(TrackRow row)
        {
            if (SBEWCostTracking) return;
            //process last field value
            var vmin = row.Fields.Find(c => c.field == "VMIN");
            var vm = row.Fields.Find(c => c.field == "VM");
            var turnover = row.Fields.Find(c => c.field == "Turnover");

            vmin.values.Keys.ToList().ForEach(p =>
            {
                vmin.values[p] = Utilities.Division(vm.values[p], turnover.values[p]);
            });
        }

        private List<TrackItem> GetDefaultFields(List<string> title)
        {

            var temp = new List<TrackItem>();
            if (SBEWCostTracking)
            {
                if (Detail)
                {
                    temp.Add(new TrackItem(title) { column = "Qty", field = "Qty", unit = CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit SBEW Cost", field = "SBEWCost", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit Freight", field = "Freight", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit Packaging", field = "Packaging", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit Exportrefund", field = "Exportrefund", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit Packing", field = "Packing", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit Rebates", field = "Rebates", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit Warehouse", field = "Warehouse", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit Commissions", field = "Commissions", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit ISOtankrental", field = "ISOtankrental", unit = "RMB/" + CEntity.Toannge });
                }
                else
                {
                    temp.Add(new TrackItem(title) { column = "Qty", field = "Qty", unit = CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "SBEW Cost", field = "SBEWCost", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "Freight", field = "Freight", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "Packaging", field = "Packaging", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "Exportrefund", field = "Exportrefund", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "Packing", field = "Packing", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "Rebates", field = "Rebates", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "Warehouse", field = "Warehouse", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "Commissions", field = "Commissions", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "ISOtankrental", field = "ISOtankrental", unit = "RMB" });
                }
            }
            else
            {
                if (Detail)
                {
                    temp.Add(new TrackItem(title) { column = "Qty", field = "Qty", unit = CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit Price", field = "Turnover", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit SBEW Cost", field = "SBEWCost", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit SBEW Price", field = "SBEWPrice", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit VC", field = "VC", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit VM", field = "VM", unit = "RMB/" + CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Unit VM in%", field = "VMIN", unit = "%" });
                }
                else
                {
                    temp.Add(new TrackItem(title) { column = "Qty", field = "Qty", unit = CEntity.Toannge });
                    temp.Add(new TrackItem(title) { column = "Turnover", field = "Turnover", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "SBEW Cost", field = "SBEWCost", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "SBEW Price", field = "SBEWPrice", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "VC", field = "VC", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "VM", field = "VM", unit = "RMB" });
                    temp.Add(new TrackItem(title) { column = "VM in%", field = "VMIN", unit = "%" });
                }
            }
            return temp;
        }

        private string CreateDate(string p, string p_2)
        {
            return BusinessService.GetEngMonth(int.Parse(p_2)) + "-" + p.Replace(",", "").Substring(2);
        }

        /// <summary>
        /// get last 12 month value  
        /// </summary> 
        public List<LoadingData> GetLast12MonthFieldValue(string coCode, int year, int month, string listName, bool withCurrentMonth)
        {
            var temp = new List<LoadingData>();

            int y = year;
            int y_1 = y - 1;
            int y_2 = y + 1;

            int m = month;
            int l_m = m + 1;

            if (withCurrentMonth)
                l_m = m;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                string url = SPContext.Current.Site.WebApplication.AlternateUrls[0].IncomingUrl + "/sites/ReportingTool";


                using (SPSite site = new SPSite(url))
                {
                    using (SPWeb web = site.RootWeb)
                    {

                        SPList list = web.Lists.TryGetList(listName);
                        if (list != null)
                        {
                            SPQuery spQuery = new SPQuery();
                            spQuery.Query = string.Format(@"<Where>
                                              <And>
                                                 <Geq>
                                                    <FieldRef Name='Year' />
                                                    <Value Type='Text'>{0}</Value>
                                                 </Geq>
                                                 <And>
                                                    <Leq>
                                                       <FieldRef Name='Year' />
                                                       <Value Type='Text'>{1}</Value>
                                                    </Leq>
                                                    <Eq>
                                                       <FieldRef Name='Company' />
                                                       <Value Type='Text'>{2}</Value>
                                                    </Eq>
                                                 </And>
                                              </And>
                                           </Where>", y_1, y_2, coCode);
                            SPListItemCollection items = list.GetItems(spQuery);
                            if (items.Count > 0)
                            {
                                int itemCount = items.Count;
                                for (int i = 0; i < itemCount; i++)
                                {
                                    var item = items[i];
                                    int itemYear = int.Parse(item["Year"] + "");
                                    int itemMonth = int.Parse(item["Period"] + "");

                                    if ((itemYear == y_1 && itemMonth >= l_m) ||
                                         (itemYear == y && itemMonth <= m))
                                    {
                                        var row = new LoadingData(item);

                                        if (RGC.ContainsKey(row.RGC))
                                            row.RGC += "-" + RGC[row.RGC];

                                        temp.Add(row);
                                    }
                                }
                            }
                        }
                    }
                }
            });

            return temp;
        }

    }
}
